﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NetworkAssetManager.DataAccess;
using NetworkAssetManager.General;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Data;
using System.Reflection;
using NetworkAssetManager.Entity;
using System.Collections;

namespace NetworkAssetManager.DataAcccess
{
    public abstract class EntityBaseDb : BaseDB
    {
        public DBCode Insert(object entity)
        {
            DBCode retCode = DBCode.Ok;
            try
            {
                using (DbTransaction transaction = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = Connection.CreateCommand())
                    {
                        command.CommandText = InsertQueryString(entity); 
                        Type t = entity.GetType();
                        PropertyInfo[] pi = t.GetProperties();

                        foreach (PropertyInfo prop in pi)
                        {
                            if (prop.Name == "ClassName" || prop.Name == "Icon" || prop.Name == "NodeName")
                            {
                                continue;
                            }
                            SqlCeParameter param = null; 
                            if (prop.PropertyType.Name == "DateTime" && DateTime.MinValue == (DateTime)prop.GetValue(entity, null))
                                param= new SqlCeParameter(prop.Name, DBNull.Value);
                            else
                                param = new SqlCeParameter(prop.Name, prop.GetValue(entity, null));

                            command.Parameters.Add(param);
                        }

                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    retCode = DBCode.Ok;
                }
            }
            catch (Exception e)
            {
                string test = e.Message;
                retCode = DBCode.Error;
            }
            return retCode;
        }

        /*
         *	Builds the dynamic Insert query for all the entities
         */
        private string InsertQueryString(object entity)
        {
            StringBuilder sb = new StringBuilder("INSERT INTO ");
            string tableName = entity.GetType().GetProperty("ClassName").GetValue(entity, null).ToString();
            sb.AppendFormat("[{0}] (", tableName);

            Type t = entity.GetType();

            PropertyInfo[] pi = t.GetProperties();

            string parameters = string.Empty;
            foreach (PropertyInfo prop in pi)
            {
                if (prop.Name == "ClassName" || prop.Name == "Icon" || prop.Name == "NodeName")
                {
                    continue;
                }

                parameters += prop.Name + ", ";
            }
            parameters = parameters.TrimEnd(',', ' ');

            sb.AppendFormat("{0}) VALUES (", parameters);

            parameters = string.Empty;
            for (int count = 0; count < pi.Length - 3; count++)
            {
                parameters += "?, ";
            }
            parameters = parameters.TrimEnd(',', ' ');

            sb.AppendFormat("{0});", parameters);
            return sb.ToString();
        }
        /*
         *	Builds the dynamic Insert query for all the entities
         */

        public int GetEntity(int scanID, EntityType entity, ref ArrayList entData)
        {
            try
            {
                using (SqlCeCommand command = Connection.CreateCommand())
                {
                    command.CommandText = SelectConditionalQueryString(entity, "where ScanID = ?");

                    command.Parameters.Add(new SqlCeParameter("ScanID",
                        SqlDbType.Int, 4)).Value = scanID;

                    using (SqlCeDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            entData.Add(DataReaderToObject(reader, entity)); 
                        }
                    }
                }
            }
            catch (Exception) 
            {
 
            }
            return 1; 
        }

        private string SelectConditionalQueryString(EntityType entity, string condition)
        {
            StringBuilder sb = new StringBuilder("SELECT * from", 100);

            string tableName = string.Empty;  

            switch (entity)
            {
                case EntityType.Processor:
                    tableName = "Processor"; 
                    break;
                case EntityType.OS:
                    tableName = "OS";
                    break;
                case EntityType.Bios:
                    tableName = "Bios";
                    break; 
                case EntityType.MotherBoard:
                    tableName = "MotherBoard"; 
                    break;
                case EntityType.Disk:
                    tableName = "Disk";
                    break;
                case EntityType.Memory:
                    tableName = "Memory";
                    break;
                case EntityType.LogicalDrive:
                    tableName = "LogicalDrive";
                    break;
                case EntityType.CDRom:
                    tableName = "CDRom";
                    break;
                case EntityType.Video:
                    tableName = "Video";
                    break;
                case EntityType.Multimedia:
                    tableName = "Multimedia";
                    break;
                case EntityType.Monitor:
                    tableName = "Monitor";
                    break;
                case EntityType.Share:
                    tableName = "Share";
                    break;
                case EntityType.StartUp:
                    tableName = "StartUp";
                    break;
                case EntityType.Hotfix:
                    tableName = "Hotfixes";
                    break;
                case EntityType.Processes:
                    tableName = "Processes";
                    break;
                case EntityType.Softwares:
                    tableName = "Softwares";
                    break;
                case EntityType.Services:
                    tableName = "Services";
                    break;
                case EntityType.IPRoutes:
                    tableName = "IPRoutes";
                    break;
                case EntityType.EnvironmentVar:
                    tableName = "EnvironmentVars";
                    break;
                case EntityType.Computer:
                    tableName = "Computer";
                    break;
                case EntityType.Printer:
                    tableName = "Printer";
                    break;
                case EntityType.UserGroup:
                    tableName = "UserGroups";
                    break; 


            }

            sb.AppendFormat(" [{0}] ", tableName);

            if ( condition!=null)
                sb.Append(condition);

            sb.Append(";");
            return sb.ToString();
        }

        private object DataReaderToObject(SqlCeDataReader reader, EntityType entity)
        {
            object entityObject  = null;

            switch (entity)
            {
                case EntityType.Processor:
                    entityObject = new EntProcessor();
                    break;
                case EntityType.OS:
                    entityObject = new EntOS();
                    break;
                case EntityType.Bios:
                    entityObject = new EntBios();
                    break;
                case EntityType.MotherBoard:
                    entityObject = new EntMotherBoard(); 
                    break;
                case EntityType.Disk:
                    entityObject = new EntDisk();
                    break;
                case EntityType.Memory:
                    entityObject = new EntMemory();
                    break;
                case EntityType.LogicalDrive:
                    entityObject = new EntLogicalDrive();
                    break;
                case EntityType.CDRom:
                    entityObject = new EntCDRom();
                    break;
                case EntityType.Video:
                    entityObject = new EntVideo();
                    break;
                case EntityType.Multimedia:
                    entityObject = new EntMultimedia();
                    break;
                case EntityType.Monitor:
                    entityObject = new EntMonitor();
                    break;
                case EntityType.Share:
                    entityObject = new EntShare();
                    break;
                case EntityType.StartUp:
                    entityObject = new EntStartUp();
                    break;
                case EntityType.Hotfix:
                    entityObject = new EntHotfixes();
                    break;
                case EntityType.Processes:
                    entityObject = new EntProcesses();
                    break;
                case EntityType.Softwares:
                    entityObject = new EntSoftwares();
                    break;
                case EntityType.Services:
                    entityObject = new EntServices();
                    break;
                case EntityType.IPRoutes:
                    entityObject = new EntIPRoutes();
                    break;
                case EntityType.EnvironmentVar:
                    entityObject = new EntEnvironmentVars();
                    break;
                case EntityType.Computer:
                    entityObject = new EntComputer();
                    break;
                case EntityType.Printer:
                    entityObject = new EntPrinter();
                    break;
                case EntityType.UserGroup:
                    entityObject = new EntUserGroups();
                    break; 


            }

            Type t = entityObject.GetType();
            PropertyInfo[] pi = t.GetProperties();

            foreach (PropertyInfo prop in pi)
            {
                if (prop.Name == "ClassName" || prop.Name == "Icon" || prop.Name == "NodeName")
                    continue; 

                switch (prop.PropertyType.Name)
                {
                    case "String":
                        string strValue = reader.GetString(reader.GetOrdinal(prop.Name));
                        prop.SetValue(entityObject, strValue, null); 
                        break; 
                    case "Int32":
                        int intValue = reader.GetInt32(reader.GetOrdinal(prop.Name));
                        prop.SetValue(entityObject, intValue, null); 
                        break; 
                    case "DateTime":
                        DateTime dtValue = DateTime.MinValue; 
                        if ( reader.GetValue(reader.GetOrdinal(prop.Name))== DBNull.Value)
                        {
                            dtValue = DateTime.MinValue; 
                        }
                        else
                        {
                            dtValue = reader.GetDateTime(reader.GetOrdinal(prop.Name));
                        }
                        prop.SetValue(entityObject, dtValue, null); 
                        break;
                    case "Boolean":
                        bool bValue = reader.GetBoolean(reader.GetOrdinal(prop.Name));
                        prop.SetValue(entityObject, bValue, null);
                        break; 

                }
            }
            
            return entityObject; 
        }

        public void RunQuery(string query, ref DataSet myDataSet, string tableName)
        {
            SqlCeDataAdapter DBAdapter = null;
            try
            {
                using (SqlCeCommand command = Connection.CreateCommand())
                {
                    command.CommandText = query;
                    DBAdapter = new SqlCeDataAdapter(query, Connection);
                    DBAdapter.Fill(myDataSet,tableName);
                }
            }
            catch (Exception e)
            {
                string test = e.Message; 
            }
        }

        public void RunQueryTable(string query, ref DataTable myDataTable)
        {
            SqlCeDataAdapter DBAdapter = null;
            try
            {
                using (SqlCeCommand command = Connection.CreateCommand())
                {
                    command.CommandText = query;
                    DBAdapter = new SqlCeDataAdapter(query, Connection);
                    DBAdapter.Fill(myDataTable);
                    DBAdapter.Dispose();
                }
            }
            catch (Exception e)
            {
                string test = e.Message;
            }
        }

    }
}
